clear all
set more off

import excel "$path_data/followup_parents_master.xlsx", clear first

local q3888999 b3 b7 ///
cm1_2 cm1_3 cm1_4 cm1_5 cm1_6 cm1_7 cm1_8 cm1_9 cm1_10 cm1_11 ///
cm2_2 cm2_3 cm2_4 cm2_5 cm2_6 cm2_7 cm2_8 cm2_9 cm2_10 cm2_11 ///
cm3_2 cm3_3 cm3_4 cm3_5 cm3_6 cm3_7 cm3_8 cm3_9 cm3_10 cm3_11 ///
cm4_2 cm4_3 cm4_4 cm4_5 cm4_6 cm4_7 cm4_8 cm4_9 cm4_10 cm4_11 ///
e2 f2_1

local yesno f1_1 f1_3 f2_1

local missingzero e9_1 e9_2 e9_3 e9_4 e9_5 e9_6 e9_7 e9_8 e9_9

foreach y in `q3888999'{
replace `y'=.  if `y'==3
replace `y'=.  if `y'==888
replace `y'=.  if `y'==999
}

foreach y in `yesno'{
replace `y'=0  if `y'==2
}

foreach y in `missingzero'{
replace `y'=0  if `y'==.
}

gen hhmember = a3
gen hhheadage = am1_3a if am1_4 == 1
replace hhheadage = am2_3a if am2_4 == 1
replace hhheadage = am3_3a if am3_4 == 1
replace hhheadage = am4_3a if am4_4 == 1
gen hhheadedu = am1_6 if am1_4 == 1
replace hhheadedu = am2_6 if am2_4 == 1
replace hhheadedu = am3_6 if am3_4 == 1
replace hhheadedu = am4_6 if am4_4 == 1

gen hhheadeduyear = hhheadedu
replace hhheadeduyear = 10 if hhheadedu == 11
replace hhheadeduyear = 0 if hhheadedu == 17
replace hhheadeduyear = 18 if hhheadedu == 15
replace hhheadeduyear = . if hhheadedu == 888
replace hhheadeduyear = . if hhheadedu == 999

destring x1d x1f x1h , replace

keep x1d x1f x1h hhmember hhheadage hhheadedu hhheadeduyear

preserve
collapse (mean) hhmember hhheadage hhheadedu hhheadeduyear, by(x1d)
replace hhheadeduyear = . if hhheadeduyear == 4.5
rename x1d student_no
save "$path_data/temp/endline_followup_parents_data_1stchild", replace

restore
preserve
rename x1f student_no
drop if student_no == .
collapse (mean) hhmember hhheadage hhheadedu hhheadeduyear, by(student_no)
replace hhheadeduyear = . if hhheadeduyear == 4.5
save "$path_data/temp/endline_followup_parents_data_2ndchild", replace

restore
preserve
rename x1h student_no
drop if student_no == .
collapse (mean) hhmember hhheadage hhheadedu hhheadeduyear, by(student_no)
replace hhheadeduyear = . if hhheadeduyear == 4.5
save "$path_data/temp/endline_followup_parents_data_3rdchild", replace


